package com.example.sspulibrary.db;

import static android.content.Context.MODE_PRIVATE;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.content.SharedPreferences;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.util.Log;

import com.example.sspulibrary.R;
import com.example.sspulibrary.model.Book;
import com.example.sspulibrary.model.ExceptBook;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author : HengZhang
 * <p>
 * 数据库操作类
 */

public class DBDefinitionManipulation {
    private static final String TAG = "DBDefinitionManipulation";
    private final DBTools dbTools = new DBTools();
    private static final String DB_NAME = "mylibrary.db";
    private static final String EXCEPT_BOOK = "t_except_book";
    private static final String ALL_BOOK_TABLE = "t_all_book";
    private static final int DB_VERSION = 1;
    Bundle bundle = new Bundle();

    //all表的列名/////////////////////////////////////////////////////////////////
    public static final String ALL_ID = "_id";
    public static final String ALL_RFID = "RFID";
    public static final String ALL_BarCode = "bar_code";
    public static final String ALL_Name = "book_name";
    public static final String ALL_BookShelf = "book_shelf";
    public static final String ALL_StoreHouse = "store_house";
    public static final String ALL_Row = "book_row";
    public static final String ALL_Face = "face";
    public static final String ALL_Series = "series";
    public static final String ALL_CreateTime = "create_time";
    public static final String ALL_UpdateTime = "update_time";
    public static final String ALL_CurrentTime = "current";
    ////////////////////////////////////////////////////////////////////
    // except_book表的列名  ///////////////////////////////////////////////////////////////
    public static final String EXCEPT_ID = "_id";
    public static final String EXCEPT_RFID = "RFID";
    public static final String EXCEPT_BookName = "book_name";
    public static final String EXCEPT_CurrentTime = "current";
    public static final String EXCEPT_CurrentLocation = "current_location";
    private static final String EXCEPT_RightLocation = "right_location";

    private SQLiteDatabase db;
    private final Context context;
    private DBOpenHelper dbOpenHelper;

    // 构造器
    public DBDefinitionManipulation(Context context) {
        this.context = context;
    }

    /******************************************************************************/
    // 添加书
    public long addBook(Book book) {
        ContentValues newValues = new ContentValues();
        newValues.put(ALL_RFID, book.getBookRFID());
        newValues.put(ALL_BarCode, book.getBookBarCode());
        newValues.put(ALL_Name, book.getBookName());
        newValues.put(ALL_BookShelf, book.getBookShelf());
        newValues.put(ALL_StoreHouse, book.getStoreHouse());

        newValues.put(ALL_Row, book.getRow());
        newValues.put(ALL_Face, book.getFace());
        newValues.put(ALL_Series, book.getSeries());
        newValues.put(ALL_CreateTime, book.getCreateTime());
        newValues.put(ALL_UpdateTime, book.getUpdateTime());
        newValues.put(ALL_CurrentTime, book.getCurrentTime());

        return db.insert(ALL_BOOK_TABLE, null, newValues);
    }

    //添加异常书
    public long addExceptBook(ExceptBook book) {
        ContentValues newValues = new ContentValues();
        newValues.put(EXCEPT_RFID, book.getBookRFID());
        newValues.put(EXCEPT_BookName, book.getBookName());
        newValues.put(EXCEPT_CurrentTime, book.getCurrentTime());
        newValues.put(EXCEPT_CurrentLocation, book.getCurrentLocation());
        newValues.put(EXCEPT_RightLocation, book.getRightLocation());

        return db.insert(EXCEPT_BOOK, null, newValues);
    }

    public void deleteAllBook() {
        String sql = "delete from " + ALL_BOOK_TABLE + " where 1=1";
        db.execSQL(sql);
    }

    public void deleteAllExceptBook() {
        String sql = "delete from " + EXCEPT_BOOK + " where 1=1";
        db.execSQL(sql);
    }

    public Book getBookByRFID(String RFID) {
        Cursor cursor = db.query(ALL_BOOK_TABLE,
                new String[]{},
                ALL_RFID + "=?",
                new String[]{RFID},
                null, null, null);
        Book book = new Book();
        if (dbTools.checkCursor(cursor)) {
            cursor.moveToFirst();

            book.setBookRFID(cursor.getString(cursor.getColumnIndex(ALL_RFID)));
            book.setBookBarCode(cursor.getString(cursor.getColumnIndex(ALL_BarCode)));
            book.setBookName(cursor.getString(cursor.getColumnIndex(ALL_Name)));
            book.setBookShelf(cursor.getString(cursor.getColumnIndex(ALL_BookShelf)));
            book.setStoreHouse(cursor.getString(cursor.getColumnIndex(ALL_StoreHouse)));
            book.setRow(cursor.getString(cursor.getColumnIndex(ALL_Row)));
            book.setFace(cursor.getString(cursor.getColumnIndex(ALL_Face)));
            book.setSeries(cursor.getString(cursor.getColumnIndex(ALL_Series)));
            book.setCreateTime(cursor.getString(cursor.getColumnIndex(ALL_CreateTime)));
            book.setUpdateTime(cursor.getString(cursor.getColumnIndex(ALL_UpdateTime)));
            book.setCurrentTime(cursor.getString(cursor.getColumnIndex(ALL_CurrentTime)));
        }
        return book;
    }

    public boolean queryByRFID(String RFID) {

        SharedPreferences preferences = context.getSharedPreferences("info", MODE_PRIVATE);

        @SuppressLint("Recycle")
        Cursor cursor = db.query(ALL_BOOK_TABLE,
                new String[]{},
                ALL_RFID + "=? and " + ALL_StoreHouse + "=? and " + ALL_Row + " =? and " + ALL_Face + " =? and " + ALL_Series + " =? ",
                new String[]{RFID, preferences.getString("searchHouse", ""), preferences.getString("searchRow", ""), preferences.getString("searchFace", ""), preferences.getString("searchSeries", "")},
                null, null, null);

        return cursor.getCount() > 0;
    }

    public String queryNameByRFID(String RFID) {

        Cursor cursor = db.query(ALL_BOOK_TABLE,
                new String[]{},
                ALL_RFID + "=?",
                new String[]{RFID},
                null, null, null);
        if (cursor.getCount() > 0) {
            cursor.moveToFirst();
            return cursor.getString(cursor.getColumnIndex(ALL_Name));
        }

        return "";
    }

    public List<ExceptBook> queryExceptBooks() {
        Cursor cursor = db.query(EXCEPT_BOOK,
                new String[]{EXCEPT_RFID, EXCEPT_BookName, EXCEPT_CurrentTime, EXCEPT_CurrentLocation, EXCEPT_RightLocation},
                null, null, null, null, null);

        return dbTools.convertToExceptBooks(cursor, EXCEPT_RFID, EXCEPT_BookName, EXCEPT_CurrentTime, EXCEPT_CurrentLocation, EXCEPT_RightLocation);

    }

    public List<Book> queryAllBooks() {
        Cursor cursor = db.query(ALL_BOOK_TABLE,
                new String[]{ALL_RFID, ALL_BarCode, ALL_Name, ALL_BookShelf, ALL_StoreHouse, ALL_Row, ALL_Face, ALL_Series, ALL_CreateTime, ALL_UpdateTime, ALL_CurrentTime},
                null, null, null, null, null);
        return dbTools.convertToBooks(cursor, ALL_RFID, ALL_BarCode, ALL_Name, ALL_BookShelf, ALL_StoreHouse, ALL_Row, ALL_Face, ALL_Series, ALL_CreateTime, ALL_UpdateTime, ALL_CurrentTime);
    }

    public void getDatas() {
        ArrayList<String> storeHouseList = new ArrayList<>();
        ArrayList<String> rowList = new ArrayList<>();
        ArrayList<String> faceList = new ArrayList<>();
        ArrayList<String> seriesList = new ArrayList<>();

        //库
        Cursor cursor1 = db.query(true, ALL_BOOK_TABLE,
                new String[]{ALL_StoreHouse},
                null,
                null,
                null, null, null, null);
        if (dbTools.checkCursor(cursor1)) {
            cursor1.moveToFirst();
            for (int i = 0; i < cursor1.getCount(); i++) {

                String storeHouse = cursor1.getString(cursor1.getColumnIndex(ALL_StoreHouse));
                storeHouseList.add(storeHouse);
                cursor1.moveToNext();
            }
        }
        Log.e("getDatas 库 ", storeHouseList.toString());

        //排
        Cursor cursor2 = db.query(true, ALL_BOOK_TABLE,
                new String[]{ALL_Row},
                null,
                null,
                null, null, null, null);
        if (dbTools.checkCursor(cursor2)) {
            cursor2.moveToFirst();
            for (int i = 0; i < cursor2.getCount(); i++) {

                String row = cursor2.getString(cursor2.getColumnIndex(ALL_Row));
                rowList.add(row);

                cursor2.moveToNext();
            }
        }
        Log.e("getDatas 排 ", rowList.toString());

        //面
        Cursor cursor3 = db.query(true, ALL_BOOK_TABLE,
                new String[]{ALL_Face},
                null,
                null,
                null, null, null, null);
        if (dbTools.checkCursor(cursor3)) {
            cursor3.moveToFirst();
            for (int i = 0; i < cursor3.getCount(); i++) {

                String face = cursor3.getString(cursor3.getColumnIndex(ALL_Face));
                faceList.add(face);


                cursor3.moveToNext();
            }
        }
        Log.e("getDatas 面 ", faceList.toString());

        //列
        Cursor cursor4 = db.query(true, ALL_BOOK_TABLE,
                new String[]{ALL_Series},
                null,
                null,
                null, null, null, null);
        if (dbTools.checkCursor(cursor4)) {
            cursor4.moveToFirst();
            for (int i = 0; i < cursor4.getCount(); i++) {

                String series = cursor4.getString(cursor4.getColumnIndex(ALL_Series));

                seriesList.add(series);


                cursor4.moveToNext();
            }
        }
        Log.e("getDatas 列 ", seriesList.toString());

        SharedPreferences DBBookInfo = context.getSharedPreferences("DBBookInfo", MODE_PRIVATE);
        SharedPreferences.Editor editor = DBBookInfo.edit();
        editor.clear();
        editor.putString("StoreHouse", storeHouseList.toString());
        editor.putString("Row", rowList.toString());
        editor.putString("Face", faceList.toString());
        editor.putString("Series", seriesList.toString());
        editor.apply();
    }

    /**************************************************************************************************************************/

    /**
     * Open the database
     * 打开数据库
     */
    public void open() throws SQLiteException {
        dbOpenHelper = new DBOpenHelper(context, DB_NAME, null, DB_VERSION);
        try {
            db = dbOpenHelper.getWritableDatabase();
        } catch (SQLiteException e) {
            db = dbOpenHelper.getReadableDatabase();
        }
    }

    /**
     * Close the database
     * 关闭数据库
     */
    public void close() {
        if (db != null) {
            db.close();
            db = null;
        }
    }

    @SuppressLint("SimpleDateFormat")
    public void readExcel(Context context) {

        InputStream stream = context.getResources().openRawResource(R.raw.books);
        try {
            XSSFWorkbook workbook = new XSSFWorkbook(stream);
            XSSFSheet sheet = workbook.getSheetAt(0);//sheet
            int rowsCount = sheet.getPhysicalNumberOfRows();//行数

            for (int r = 1; r < rowsCount; r++) {

                Book book = new Book();
                Row row = sheet.getRow(r);//行
                int cellsCount = row.getPhysicalNumberOfCells();//每行单元格数
                row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
                row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
                row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
                row.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
                row.getCell(6).setCellType(Cell.CELL_TYPE_STRING);
                row.getCell(7).setCellType(Cell.CELL_TYPE_STRING);
                row.getCell(8).setCellType(Cell.CELL_TYPE_STRING);
                book.setBookRFID(row.getCell(0).getStringCellValue());
                book.setBookBarCode(row.getCell(1).getStringCellValue());
                book.setBookCallNumber(row.getCell(2).getStringCellValue());
                book.setBookName(row.getCell(3).getStringCellValue());
                book.setBookShelf(row.getCell(5).getStringCellValue());
                book.setStoreHouse(row.getCell(4).getStringCellValue());
                book.setRow(row.getCell(6).getStringCellValue());
                book.setFace(row.getCell(7).getStringCellValue());
                book.setSeries(row.getCell(8).getStringCellValue());
                book.setCreateTime(new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(new Date()));
                book.setUpdateTime("");
                book.setCurrentTime("");

                addBook(book);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
        }
    }

    /**
     * 静态Helper类，用于建立、更新和打开数据库
     */
    private static class DBOpenHelper extends SQLiteOpenHelper {

        public DBOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
            super(context, name, factory, version);
        }

        private static final String CREATE_ALL_BOOK_TABLE = "create table " + EXCEPT_BOOK + " ( "
                + EXCEPT_ID + " integer primary key autoincrement, "
                + EXCEPT_RFID + " text, "
                + EXCEPT_BookName + " text , "
                + EXCEPT_CurrentLocation + " text, "
                + EXCEPT_CurrentTime + " text, "
                + EXCEPT_RightLocation + " text ); ";

        private static final String CREATE_EXCEPT_TABLE = "create table " + ALL_BOOK_TABLE + " ( "
                + ALL_ID + " integer primary key autoincrement, "
                + ALL_RFID + " text , "
                + ALL_BarCode + " text , "
                + ALL_Name + " text , "
                + ALL_BookShelf + " text , "
                + ALL_StoreHouse + " text , "
                + ALL_Row + " text , "
                + ALL_Face + " text , " + ALL_Series + " text, "
                + ALL_UpdateTime + " text, "
                + ALL_CurrentTime + " text, "
                + ALL_CreateTime + " text ); ";

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(CREATE_EXCEPT_TABLE);
            db.execSQL(CREATE_ALL_BOOK_TABLE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int _oldVersion, int _newVersion) {
            db.execSQL("DROP TABLE IF EXISTS " + EXCEPT_BOOK);
            db.execSQL("DROP TABLE IF EXISTS " + ALL_BOOK_TABLE);
            onCreate(db);
        }
    }
}


